package il.ac.tau.cvws.scrabblesidekick.server.db.template;

import java.util.List;

import il.ac.tau.cvws.scrabblesidekick.server.db.dao.WordsNDao;
import il.ac.tau.cvws.scrabblesidekick.shared.constant.BoardConstants;

public class DBQueryTemplate 
{
	
	/*
	 * LOGIC: the following function retrieve the query string to execute in order to find words that fit the template. 
	 * to do so, the function iterate over the letters of the given template and keep the indices of the non empty letters in array. 
	 * Then, the function calls one of the functions above according to the number of non empty letters found in template and send the created index array.
	 */
	public String queryWord(int dictionaryId, List<Integer> wordTemp)
	{
		int[] indexWord = new int[wordTemp.size()];
		int indexCount = 0; 
		int j = 0;
		int[] word = new int[wordTemp.size()];
		
		for(Integer i: wordTemp)
		{
			word[j] = i;
			j++;
		}
		
		for(int i = 0; i < word.length ; i++)
		{
			if (word[i] != BoardConstants.EMPTY_TILE)
			{
				indexWord[indexCount] = i; 
				indexCount++;
			}
		}
		
		int length = word.length;
		
		switch (indexCount - 1)
		{
		case -1: return querySomeWord(dictionaryId, length);
		case 0:  return queryWord1(dictionaryId, indexWord, word, length);
		case 1:  return queryWord2(dictionaryId, indexWord, word, length);
		case 2:  return queryWord3(dictionaryId, indexWord, word, length);
		case 3:  return queryWord4(dictionaryId, indexWord, word, length);
		case 4:  return queryWord5(dictionaryId, indexWord, word, length);
		case 5:  return queryWord6(dictionaryId, indexWord, word, length);
		case 6:  return queryWord7(dictionaryId, indexWord, word, length);
		case 7:  return queryWord8(dictionaryId, indexWord, word, length);
		case 8:  return queryWord9(dictionaryId, indexWord, word, length);
		case 9:  return queryWord10(dictionaryId, indexWord, word,length);
		case 10: return queryWord11(dictionaryId, indexWord, word,length);
		case 11: return queryWord12(dictionaryId, indexWord, word,length);
		case 12: return queryWord13(dictionaryId, indexWord, word,length);
		case 13: return queryWord14(dictionaryId, indexWord, word,length);
		case 14: return queryWord15(dictionaryId, indexWord, word,length);
		
		default: return null;
		}
	}


	
	private String querySomeWord(int dictionaryId, int size) 
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(size - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " + getColumn(size) + " != " + BoardConstants.EMPTY_TILE;
	}

/*
 * LOGIC: all the function above search for words which fit the given template. queryWordN- means that there are only N existing non-empty tiles in template
 * the indices of the non empty letters are placed sequentially in indexNonEmpty array.
 */
	private String queryWord1(int dictionaryId, int[] indexNonEmpty ,int[] word, int length )
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0]) + " = " + word[indexNonEmpty[0]]    +  " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " + 
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord2(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0]) + " = " + word[indexNonEmpty[0]] +     " AND " + 
				getColumn(indexNonEmpty[1]) + " = " + word[indexNonEmpty[1]]+      " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " + 
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord3(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0]) + " = " + word[indexNonEmpty[0]] +     " AND " + 
				getColumn(indexNonEmpty[1]) + " = " + word[indexNonEmpty[1]] +     " AND " + 
				getColumn(indexNonEmpty[2]) + " = " + word[indexNonEmpty[2]]+      " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord4(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +    " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +    " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +    " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]]+     " AND " + 
				getColumn(length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord5(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +    " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +    " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +    " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +    " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +    " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord6(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +    " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +    " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +    " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +    " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +    " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +    " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord7(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +    " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +    " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +    " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +    " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +    " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +    " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]]+     " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord8(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]]+   " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord9(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " + 
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
			    getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord10(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " + 
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord11(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " +
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " +
				getColumn(indexNonEmpty[10])  + " = " + word[indexNonEmpty[10]] +  " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord12(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " +
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " +
				getColumn(indexNonEmpty[10])  + " = " + word[indexNonEmpty[10]] +  " AND " +
				getColumn(indexNonEmpty[11])  + " = " + word[indexNonEmpty[11]]+ " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord13(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " +
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " +
				getColumn(indexNonEmpty[10])  + " = " + word[indexNonEmpty[10]] +  " AND " +
				getColumn(indexNonEmpty[11])  + " = " + word[indexNonEmpty[11]] +  " AND " +
				getColumn(indexNonEmpty[12])  + " = " + word[indexNonEmpty[12]]+ " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord14(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " +
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " +
				getColumn(indexNonEmpty[10])  + " = " + word[indexNonEmpty[10]] +  " AND " +
				getColumn(indexNonEmpty[11])  + " = " + word[indexNonEmpty[11]] +  " AND " +
				getColumn(indexNonEmpty[12])  + " = " + word[indexNonEmpty[12]] +  " AND " +
				getColumn(indexNonEmpty[13])  + " = " + word[indexNonEmpty[13]] +  " AND " + 
				getColumn(word.length - 1) + " !=" + BoardConstants.EMPTY_TILE + " AND " +
				getColumn(word.length) + " = " + BoardConstants.EMPTY_TILE;
	}

	private String queryWord15(int dictionaryId, int[] indexNonEmpty, int[] word, int length)
	{
		return "SELECT " + "*" + " FROM " + WordsNDao.getName() +
				" WHERE " + 	WordsNDao.DICTIONARY_ID + " = " + dictionaryId +  " AND " + 
				getColumn(indexNonEmpty[0])  + " = " + word[indexNonEmpty[0]] +  " AND " + 
				getColumn(indexNonEmpty[1])  + " = " + word[indexNonEmpty[1]] +  " AND " + 
				getColumn(indexNonEmpty[2])  + " = " + word[indexNonEmpty[2]] +  " AND " + 
				getColumn(indexNonEmpty[3])  + " = " + word[indexNonEmpty[3]] +  " AND " + 
				getColumn(indexNonEmpty[4])  + " = " + word[indexNonEmpty[4]] +  " AND " + 
				getColumn(indexNonEmpty[5])  + " = " + word[indexNonEmpty[5]] +  " AND " + 
				getColumn(indexNonEmpty[6])  + " = " + word[indexNonEmpty[6]] +  " AND " + 
				getColumn(indexNonEmpty[7])  + " = " + word[indexNonEmpty[7]] +  " AND " +
				getColumn(indexNonEmpty[8])  + " = " + word[indexNonEmpty[8]] +  " AND " + 
				getColumn(indexNonEmpty[9])  + " = " + word[indexNonEmpty[9]] +  " AND " +
				getColumn(indexNonEmpty[10])  + " = " + word[indexNonEmpty[10]] +  " AND " +
				getColumn(indexNonEmpty[11])  + " = " + word[indexNonEmpty[11]] +  " AND " +
				getColumn(indexNonEmpty[12])  + " = " + word[indexNonEmpty[12]] +  " AND " +
				getColumn(indexNonEmpty[13])  + " = " + word[indexNonEmpty[13]] +  " AND " +
				getColumn(indexNonEmpty[14])  + " = " + word[indexNonEmpty[14]];
	}
	
	/*
	 * LOGIC: this function return the name of the ith column in word table
	 */
	public String getColumn(int i) 
	{
		switch (i)
		{
		case 0: return WordsNDao.LETTER_0;
		case 1: return WordsNDao.LETTER_1;
		case 2: return WordsNDao.LETTER_2;
		case 3: return WordsNDao.LETTER_3;
		case 4: return WordsNDao.LETTER_4;
		case 5: return WordsNDao.LETTER_5;
		case 6: return WordsNDao.LETTER_6;
		case 7: return WordsNDao.LETTER_7;
		case 8: return WordsNDao.LETTER_8;
		case 9: return WordsNDao.LETTER_9;
		case 10: return WordsNDao.LETTER_10;
		case 11: return WordsNDao.LETTER_11;
		case 12: return WordsNDao.LETTER_12;
		case 13: return WordsNDao.LETTER_13;
		case 14: return WordsNDao.LETTER_14;
		
		default: return null;
		}
	}
}